﻿Imports System.Data.SqlClient

Public Class Frm_Recensement
    Dim i As Integer = 0
    Dim lstDosNnRegler As List(Of etatMontan)
    Dim filter As String = ""
    Dim m As Integer = 1
    Friend WithEvents ColumnHeader2 As System.Windows.Forms.ColumnHeader
    Public Structure etatMontan
        Public idDos As Integer
        Public ref As Integer
        Public numDossier As String
        Public nomClt As String
        Public nomAdv As String
        Public reste As Double
    End Structure

    Private Sub Frm_makhssoura_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ColumnHeader2 = CType(New ColumnHeader(), ColumnHeader)
        ColumnHeader2.Width = 265
        Me.lstAlea.Columns.AddRange(New System.Windows.Forms.ColumnHeader() {Me.ColumnHeader2})
        cmbChoix.SelectedIndex = 0

    End Sub

    Private Sub cmbChoix_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbChoix.SelectedIndexChanged, Date_min.ValueChanged, Date_illa.ValueChanged
        Select Case cmbChoix.SelectedIndex
            Case 0
                DataGridView1.Rows.Clear()
                lstAlea.Items.Clear()
                'reformater les contrôles
                'Datagrid
                With DataGridView1
                    .Columns.Clear()
                    .ColumnCount = 9
                    .Columns(0).Visible = False
                    .Columns(1).Name = "المحكمـة"
                    .Columns(1).Width = 130
                    .Columns(2).Name = "رقم الملف"
                    .Columns(3).Name = "المقــرر"
                    .Columns(4).Name = "الموكـل"
                    .Columns(5).Name = "الخصـم"
                    .Columns(6).Name = "محامي الخصم"
                    .Columns(7).Name = "الحكم أو القرار"
                    .Columns(7).Width = 200
                    .Columns(8).Name = "تاريخ الحكم"
                End With

                'listView
                ColumnHeader2.Text = "قضايا"

                'requête
                groupCount("select [Resume],COUNT(*) as compt from Decision " & _
                              "where [Type] like 'قطعي' " & _
                              "and Date_Decision between '" & Format(Date_min.Value.Date, "yyyy/MM/dd") & "' and '" & Format(Date_illa.Value.Date, "yyyy/MM/dd") & "' " & _
                              "group by [Resume]")

                'remplir la list view
                If Not Partager.dr.IsClosed Then
                    If Partager.dr.HasRows Then
                        lstAlea.Items.Clear()
                        While Partager.dr.Read
                            lstAlea.Items.Add(Partager.dr.GetInt32(1) & " " & Partager.dr.GetString(0))
                        End While
                    End If
                    Partager.dr.Close()
                    Partager.close()
                    Partager.cmd.Dispose()
                End If
            Case Else
                filter = ""
                If Partager.moi.type Then
                    If sender Is cmbChoix Then
                        lstDosNnRegler = New List(Of etatMontan)
                        ColumnHeader2.Text = "أتعاب"
                        With DataGridView1
                            .Columns.Clear()
                            .ColumnCount = 5
                            .Columns(0).Visible = False
                            .Columns(1).Name = "رقم الملف"
                            .Columns(2).Name = "الموكـل"
                            .Columns(3).Name = "الخصم"
                            .Columns(4).Name = "الباقي"
                        End With

                        Dim nbrDosNnRgler As Integer = 0
                        Partager.moi.remplir_List_info_fichier()
                        For Each dos As Dossier In Partager.moi.List_info_fichier
                            dos.remplir_List_montant()

                            Dim requis As Double = 0
                            For Each khib As Montant In dos.List_montant
                                If khib.Type = "المبلغ المطلوب" Then
                                    requis += khib.Montant
                                End If
                            Next

                            Dim donne As Double = 0
                            For Each khib As Montant In dos.List_montant
                                If khib.Type = "المبلغ المودع" Then
                                    donne += khib.Montant
                                End If
                            Next
                            If requis > donne Then
                                nbrDosNnRgler += 1
                                filter &= dos.Ref & ","
                                lstDosNnRegler.Add(New etatMontan With {.idDos = dos.id, .numDossier = dos.Num, .reste = requis - donne, .ref = dos.Ref})
                            End If
                        Next
                        If filter <> "" Then
                            filter = filter.Remove(filter.Length - 1, 1)
                            lstAlea.Items.Clear()
                            lstAlea.Items.Add(lstDosNnRegler.Count & " ملف " & "غير مدفوع")
                        End If
                    End If
                Else
                    MsgBox("ليست لديك الصلاحية للدخول", MsgBoxStyle.Critical, "خطأ")
                End If
        End Select
    End Sub

    Public Sub groupCount(ByVal req As String)
        Partager.cmd = New SqlCommand(req, Partager.con)
        Partager.open()
        Partager.dr = Partager.cmd.ExecuteReader
    End Sub
    'Filtrer par le resumer de la decision
    Public Function filtrer(ByVal t As String) As String
        Return "select t.idDossier,t.Tribunal,t.Num_Dossier,t.Nom,t1.cltNomComplet,t1.cltNom_entreprise,adv.Nom_entreprise,adv.nomCmplet,t2.nomAvAdv,t.Declaration,t.Date_Decision from " &
"(select dos.id as idDossier,dos.Ref,dos.Tribunal,dos.Num_Dossier,decid.Nom,deci.Declaration,deci.Date_Decision from Dossier as dos inner join Decision as deci " &
"on dos.Id = deci.Id_Dossier " &
"left join Mo9arir as decid " &
"on decid.Id_Dossier = dos.id " &
"where deci.[Type] like 'قطعي' " &
"and Date_Decision between '" & Format(Date_min.Value.Date, "yyyy/MM/dd") & "' and '" & Format(Date_illa.Value.Date, "yyyy/MM/dd") & "' " & _
"and deci.[Resume] like '" & t & "')t " &
"left join " &
"(select ds.Id_Avocat_Adversaire,ds.Id_Dossier,ac.Nom + ' ' + ac.Prenom as nomAvAdv from Dossier_Avocat_Adversaire ds " &
"inner join Avocat_Adversaire ac " &
"on ds.Id_Avocat_Adversaire = ac.id)t2 " &
"on t.idDossier = t2.Id_Dossier " &
"left join " &
"(select clt.Nom + ' ' + clt.Prenom as cltNomComplet,clt.Nom_entreprise as cltNom_entreprise,refClt.idRef from " &
"Clients as clt " &
"inner join refClt on clt.Id = refClt.idClt )t1 " &
"on t.Ref = t1.idRef " &
"left join " &
"(select adv.Nom + ' ' + adv.Prenom as nomCmplet,adv.Nom_entreprise,refAdv.idRef from " &
"Adversaire as adv " &
"inner join refAdv on adv.Id = refAdv.idAdv " &
")adv " &
"on t.Ref = adv.idRef"
    End Function

    Private Sub lstAlea_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstAlea.SelectedIndexChanged
        If lstAlea.SelectedItems.Count > 0 Then
            Dim cmpLigne As Integer = 0
            Dim cmpColone As Int16 = 0
            Dim cmpColDtaGrd As Int16 = 0
            DataGridView1.Rows.Clear()
            Select Case cmbChoix.SelectedIndex
                Case 0
                    groupCount(filtrer(lstAlea.SelectedItems(0).SubItems(0).Text.Split(" ")(1)))
                    If Not Partager.dr.IsClosed Then
                        If Partager.dr.HasRows Then
                            While Partager.dr.Read
                                DataGridView1.Rows.Add(New DataGridViewRow)
                                cmpColDtaGrd = 0
                                For cmpColone = 0 To Partager.dr.FieldCount - 1

                                    'parce que y'a des noms vides et parfois null
                                    If cmpColone = 4 Or cmpColone = 5 Or cmpColone = 6 Or cmpColone = 7 Then
                                        If Not IsDBNull(Partager.dr(cmpColone)) Then
                                            If Partager.dr(cmpColone) = " " Or Partager.dr(cmpColone) = "" Then
                                                Continue For
                                            End If
                                        Else
                                            If cmpColone = 5 Or cmpColone = 7 Then
                                                Continue For
                                            End If
                                        End If
                                    End If
                                    If IsDBNull(Partager.dr(cmpColone)) Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColDtaGrd).Value = ""
                                    ElseIf TypeOf Partager.dr(cmpColone) Is Date Then
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColDtaGrd).Value = Partager.dr.GetDateTime(cmpColone).ToShortDateString
                                    Else
                                        DataGridView1.Rows(cmpLigne).Cells(cmpColDtaGrd).Value = Partager.dr(cmpColone)
                                    End If
                                    cmpColDtaGrd += 1
                                Next
                                cmpLigne += 1
                            End While
                        End If
                    End If
                Case Else
                    Partager.cmd = New SqlCommand("select clt.Nom + ' ' + clt.Prenom as nomComplet,clt.Nom_entreprise,adv.Nom + ' ' + adv.Prenom as nomComplet,adv.Nom_entreprise,refClt.idRef " &
                                                "from refClt " &
                                                "inner join Clients clt " &
                                                "on refClt.idClt = clt.Id " &
                                                "inner join refAdv " &
                                                "on refClt.idRef = refAdv.idRef " &
                                                "inner join Adversaire adv " &
                                                "on refAdv.idAdv = adv.id " &
                                                "where refClt.idRef in (" & filter & ")", Partager.con)
                    Partager.open()
                    Partager.dr = Partager.cmd.ExecuteReader
                    If Partager.dr.HasRows Then
                        cmpLigne = 0
                        While Partager.dr.Read
                            For Each eta In lstDosNnRegler
                                If eta.ref = Partager.dr.GetInt32(4) Then
                                    If Partager.dr(0) <> "" Or Not String.IsNullOrWhiteSpace(Partager.dr(0)) Then
                                        eta.nomClt = Partager.dr(0)
                                    End If

                                    If Partager.dr(1) <> "" Or Not String.IsNullOrWhiteSpace(Partager.dr(1)) Then
                                        eta.nomClt = Partager.dr(1)
                                    End If

                                    If Partager.dr(2) <> "" Or Not String.IsNullOrWhiteSpace(Partager.dr(2)) Then
                                        eta.nomAdv = Partager.dr(2)
                                    End If

                                    If Partager.dr(3) <> "" Or Not String.IsNullOrWhiteSpace(Partager.dr(3)) Then
                                        eta.nomAdv = Partager.dr(3)
                                    End If

                                    DataGridView1.Rows.Add(New DataGridViewRow)
                                    DataGridView1.Rows(cmpLigne).Cells(0).Value = eta.idDos
                                    DataGridView1.Rows(cmpLigne).Cells(1).Value = eta.numDossier
                                    DataGridView1.Rows(cmpLigne).Cells(2).Value = eta.nomClt
                                    DataGridView1.Rows(cmpLigne).Cells(3).Value = eta.nomAdv
                                    DataGridView1.Rows(cmpLigne).Cells(4).Value = eta.reste
                                End If
                            Next
                            cmpLigne += 1
                        End While
                    End If
            End Select
            If DataGridView1.Rows.Count > 0 Then
                DataGridView1.Rows(0).Selected = False
                DataGridView1.Rows(0).Selected = True
            End If
        End If

        For Each cel As DataGridViewColumn In DataGridView1.Columns
            cel.DefaultCellStyle.WrapMode = DataGridViewTriState.True
            cel.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            cel.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next

        If Not Partager.dr.IsClosed Then
            Partager.dr.Close()
            Partager.close()
            Partager.cmd.Dispose()
        End If
    End Sub
    Dim row As DataGridViewRow

    Private Sub DataGridView1_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.DoubleClick
        If cmbChoix.SelectedIndex = 1 Then
            If DataGridView1.SelectedRows.Count > 0 Then
                If Not DataGridView1.SelectedRows(0).Cells Is Nothing Then
                    row = DataGridView1.SelectedRows(0)
                    If Not row.Cells(0).Value Is Nothing Then

                        Dim req As IEnumerable(Of DictionaryEntry) = From hs As DictionaryEntry In Frm_at3ab.Comb_marji3ona.Items
                                                                    Where hs.Key = row.Cells(0).Value
                                                                    Select hs


                        Partager.StandardInitial()
                        Frm_at3ab.MdiParent = Frm_Main
                        Frm_at3ab.Show()
                        Frm_at3ab.Comb_marji3ona.SelectedItem = req(0)
                    End If
                End If
            End If
        End If
    End Sub

    Private Sub PrintPreviewDialog1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrintPreviewDialog1.Load

    End Sub
    Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Dim format As New StringFormat(StringFormatFlags.DirectionRightToLeft)
        Dim heigth As Integer = 100
        Dim width As Integer = 0
        Dim p As New Pen(Brushes.Black, 2.0F)
        Dim f As New System.Drawing.Font("times new roman", 9, FontStyle.Bold)
        'Dim fe As New System.Drawing.Font("times new roman", 9)
        '--------------------------------------------------
        e.Graphics.DrawString("الصفحة :" & m, f, Brushes.Black, New Point(50, 770))

        For i As Integer = DataGridView1.Columns.Count - 1 To 1 Step -1
            e.Graphics.FillRectangle(Brushes.DarkGray, New Rectangle(heigth, 100, DataGridView1.Columns(i).Width, DataGridView1.ColumnHeadersHeight))
            e.Graphics.DrawRectangle(p, New Rectangle(heigth, 100, DataGridView1.Columns(i).Width, DataGridView1.ColumnHeadersHeight))
            e.Graphics.DrawString(DataGridView1.Columns(i).HeaderText.ToString, f, Brushes.Black, New Rectangle(heigth - 2, 100 + 2, DataGridView1.Columns(i).Width + 2, DataGridView1.ColumnHeadersHeight + 2), format)

            heigth = heigth + DataGridView1.Columns(i).Width
        Next
        '--------------------------------------------------
        heigth = 100 + DataGridView1.ColumnHeadersHeight

        While i <= DataGridView1.Rows.Count - 2

            If heigth >= e.MarginBounds.Height + 100 Or heigth + DataGridView1.Rows(i).Height > e.MarginBounds.Height + 100 Then
                e.HasMorePages = True
                heigth = 100
                m += 1
                Return
            End If

            width = 100
            For j As Integer = DataGridView1.Columns.Count - 1 To 1 Step -1
                e.Graphics.DrawRectangle(p, New Rectangle(width, heigth, DataGridView1.Columns(j).Width, DataGridView1.Rows(i).Height + 40))
                e.Graphics.DrawString(DataGridView1.Rows(i).Cells(j).FormattedValue.ToString, DataGridView1.Font, Brushes.Black, New Rectangle(width + 1, heigth + 1, DataGridView1.Columns(j).Width - 1, DataGridView1.Rows(i).Height + 40), format)
                width += DataGridView1.Columns(j).Width
            Next
            heigth += DataGridView1.Rows(i).Height + 40

            i += 1
            If i = DataGridView1.Rows.Count - 2 Then
                i = 0
                m = 1
                Exit While
            End If
        End While
    End Sub

    Private Sub btn__Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_.Click
        '-------------------paysage-----------------------
        PrintDocument1.DefaultPageSettings.Landscape = True
        '-------------------------------------------------
        i = 0
        m = 1
        PrintPreviewDialog1.ShowDialog()
    End Sub
End Class